package com.szholly.pro.watergas_server.ps.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.szholly.data.general.*;
import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.utils.spring.SpringBeanFactory;
import org.springframework.stereotype.Service;

/**
 * 水样比对服务
 * 
 * @author Administrator
 *
 */
@Service
public class WaterSampleService {

	/**
	 * 获取水样比对记录，用于运维管理的界面展示和报表导出
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getWaterSampleList(Map<String, String> params, PageInfo pageInfo) throws Exception {

		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();

		String sql = " select t1.*, t2.START_TIME as TASK_START_TIME, t2.END_TIME as TASK_END_TIME, t2.FINISH_TIME as TASK_FINISH_TIME, "
				+ " t3.name as WWTP_NAME,t4.name as STATION_NAME, t4.flow_type, t5.name DEVICE_ALIAS_NAME, t6.c_userealname as MEASURE_PERSON_NAME, "
				+ " t7.TASK_CYCLE, t7.TASK_TOLERANCE,"
				+ " d1.S_DOMAINCAPTION as MEASURE_ITEM_NAME, d2.S_DOMAINCAPTION as FLOW_TYPE_NAME, d3.S_DOMAINCAPTION as MEASURE_CONCLUSION_NAME,\n "
				+ " row_number() over(partition by t1.device_type order by t1.device_number) row_number "
				+ " from OP_SAMPLE_COMP t1 " + " left join OPERATION_TASK t2 on t1.TASK_ID = t2.TASK_CODE "
				+ " left join BC_ENTERPRISE_ATTR t3 on t2.WWTP_ID = t3.EXP_NO "
				+ " left join BC_SITE_INFO t4 on t2.STATION_ID = t4.EXP_NO "
				+ " left join BC_SITE_DEVICE_RPT  t5 on t1.DEVICE_NUMBER = t5.device_num "
				+ " left join safe_user t6 on t1.MEASURE_PERSON = t6.s_userid "
				+ " left join OC_TASK_CONFIG t7 on t7.guid = t2.operations_task_scheduler_id "
				+ " left join DATA_DEFULATDOMAIN d1 on ( d1.S_DOMAINNAME = 'MONITOR_PARAM' AND d1.S_DOMAINCODE = t1.MEASURE_ITEM ) "
				+ " left join DATA_DEFULATDOMAIN d2 on ( d2.S_DOMAINNAME = 'STATION_FLOW_TYPE' AND d2.S_DOMAINCODE = t4.FLOW_TYPE ) "
				+ " left join DATA_DEFULATDOMAIN d3 on ( d3.S_DOMAINNAME = 'OPERATION_COMPARISON_CONCLUSION' AND d3.S_DOMAINCODE = t1.MEASURE_CONCLUSION ) "
				+ " where 1=1";

		if (params.containsKey("task_code")) {
			sql += " and t1.TASK_ID = @task_code";
			listParam.add(new GlobalParameter("task_code", params.get("task_code")));
		} 
			if (params.containsKey("wwtp_id")) {
				sql += " and t4.WWTP_ID = @wwtpid";
				listParam.add(new GlobalParameter("wwtpid", params.get("wwtp_id")));
			}
			if (params.containsKey("station_id")) {
				sql += " and t2.STATION_ID = @station_id";
				listParam.add(new GlobalParameter("station_id", params.get("station_id")));
			}
			if (params.containsKey("flow_type")) {
				sql += " and t4.FLOW_TYPE = @flow_type";
				listParam.add(new GlobalParameter("flow_type", params.get("flow_type")));
			}
			if (params.containsKey("water_type")) {
				sql += " and t4.station_category = @water_type";
				listParam.add(new GlobalParameter("water_type", params.get("water_type")));
			}
			if (params.containsKey("measure_item")) {
				sql += " and t1.MEASURE_ITEM in (" + params.get("measure_item") + ")";
			}
			if (params.containsKey("measure_person")) {
				sql += " and t1.MEASURE_PERSON = @measure_person";
				listParam.add(new GlobalParameter("measure_person", params.get("measure_person")));
			}
			if (params.containsKey("measure_conclusion")) {
				sql += " and t1.MEASURE_CONCLUSION = @measure_conclusion";
				listParam.add(new GlobalParameter("measure_conclusion", params.get("measure_conclusion")));
			}
			if (params.containsKey("start")) {
				sql += " and t1.MEASURE_START_TIME >= to_date('" + params.get("start")
						+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if (params.containsKey("end")) {
				sql += " and t1.MEASURE_START_TIME <= to_date('" + params.get("end")
						+ " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			}
			
			sql+=" order by t1.device_type";
		

		return DataUtil.getPageRow(sql, listParam, null, pageInfo);
	}
	/**
	 * 获取水样比对记录，用于运维管理的界面展示和报表导出
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getWaterSampleDeviceList(Map<String, String> params) throws Exception {

		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();

		String sql = " select t.device_name, t.device_type,t.device_number,t2.c_userealname as measure_person_name\n" + 
				"  from OP_SAMPLE_COMP t\n" + 
				" left join BC_SITE_INFO t4 on t.STATION_ID = t4.EXP_NO \n"+
				" left join safe_user t2 on t.measure_person=t2.s_userid"
				+ " where 1=1";

			if (params.containsKey("task_code")) {
				sql += " and t.TASK_ID = @task_code";
				listParam.add(new GlobalParameter("task_code", params.get("task_code")));
			} 
			if (params.containsKey("wwtp_id")) {
				sql += " and t4.WWTP_ID = @wwtpid";
				listParam.add(new GlobalParameter("wwtpid", params.get("wwtp_id")));
			}
			if (params.containsKey("station_id")) {
				sql += " and t.STATION_ID = @station_id";
				listParam.add(new GlobalParameter("station_id", params.get("station_id")));
			}
			if (params.containsKey("water_type")) {
				sql += " and t4.station_category = @water_type";
				listParam.add(new GlobalParameter("water_type", params.get("water_type")));
			}
			sql += " group by t.device_name, t.device_type,t.device_number,t2.c_userealname order by t.device_type\n";
		return DataUtil.getSelectRows(sql, listParam);
	}
	/**
	 * 获取水样比对任务数据记录列表，用于有效性审核
	 * 
	 * @param params
	 * @return
	 */
	public List<RowBase> getSampleComparisonDataList(Map<String, String> params) {

		List<GlobalParameter> listParams = new ArrayList<>();

		String sql = "select t.TASK_CODE,\n" 
				+ "       t.TASK_STATUS,\n" 
				+ "       t.START_TIME as TASK_START_TIME,\n"
				+ "       t.END_TIME as TASK_END_TIME,\n" 
				+ "       t.FINISH_TIME as TASK_FINISH_TIME,\n"
				+ "       t1.*,\n" 
				+ "       t2.NAME as STATION_NAME,\n" 
				+ "       t3.GROUP_NAME as GROUP_NAME,\n"
				+ "       t4.C_USEREALNAME as USER_NAME\n" 
				+ "  from OP_TASK t\n"
				+ " inner join OP_SAMPLE_COMP t1 ON t1.TASK_ID = t.TASK_CODE\n"
				+ "  left join BC_SITE_INFO t2 on t2.EXP_NO = t.STATION_ID\n"
				+ "  left join OC_GROUP t3 on t3.GUID = t.OPERATION_GROUP_ID\n"
				+ "  left join SAFE_USER t4 on t4.S_USERID = t.FINISH_BY where 1=1";
		
		if(params.containsKey("STATION_ID")){
			sql += " and t.STATION_ID=@STATION_ID";
			listParams.add(new GlobalParameter("STATION_ID",params.get("STATION_ID")));
		}
		if(params.containsKey("JOB_ID")){
			sql += "  and t.JOB_ID=@JOB_ID";
			listParams.add(new GlobalParameter("JOB_ID",params.get("JOB_ID")));
		}
		if(params.containsKey("PARAM_ID")){
			sql += "  and t1.MEASURE_ITEM=@PARAM_ID";
			listParams.add(new GlobalParameter("PARAM_ID",params.get("PARAM_ID")));
		}
		if(params.containsKey("START_TIME")){
			sql += "  and t.START_TIME >= to_date('" + params.get("START_TIME") + " 00:00:00', 'yyyy-mm-dd hh24:mi:ss')";
		}
		if(params.containsKey("END_TIME")){
			sql += "  and t.START_TIME <= to_date('" + params.get("END_TIME") + " 23:59:59', 'yyyy-mm-dd hh24:mi:ss')";
		}

		sql += " order by t.START_TIME desc";

		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("TASKQUERYDIC");
		List<RowBase> rcdList = DataUtil.getSelectRows(sql, listParams, pTable);

		return rcdList;
	}
}
